var express = require('express');
const mysql = require('../util/mysql');
var router = express.Router();
var db=require('../util/mysql');

/* GET home page. */
router.all('*', function (req, res, next) {
  res.header('Access-Control-Allow-Origin', '*');
  res.header('Access-Control-Allow-Headers', 'Content-Type');
  res.header('Access-Control-Allow-Methods', '*');
  res.header('Content-Type', 'application/json;charset=utf-8');
  next();
});
router.get('/', function(req, res, next) {
	var sql="select * from tea_users";
	var sqlArr=[];
	var callBack=(err,data)=>{
		if(err){
			console.log('连接失败')
		}else{
			res.send({
				'list':data
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
router.post("/api/login",function(req,res,next){
	
	const name=req.body.username;
	const pas=req.body.password;
	var sql="select * from tea_users where user_name=? and password=?";
	var callBack=(err,data)=>{
		if(err){
			console.log("连接失败",err)
		}else{
			if(data.length>0){
				res.send({
					'list':data,
					'code':0,
				})
			}
			else{
				res.send({
					'code':1,
				})
				console.log("登录失败")
			}
			
		}
	}
	db.sqlConnection(sql,[name,pas],callBack);
});
router.post("/api/register",function(req,res,next){
	const name=req.body.username;
	const pas=req.body.password;
	const phon=req.body.phone;
	const imge=req.body.logo;
	var sql="INSERT INTO tea_users(user_name,phone,password,user_logo) VALUES(?,?,?,?)";
	var callBack=(err,data)=>{
		if(err){
			console.log("连接失败",err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,[name,phon,pas,imge],callBack);
});
router.get("/api/swiperList",function(req,res,next){
	var sql="select * from lunbo";
	var sqlArr=[];
	var callBack=(err,data)=>{
		if(err){
			console.log('连接失败')
		}else{
			res.send({
				'list':data,
				'code':200
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//请求火热商品列表
router.get("/api/hotList",function(req,res,next){
	var sql="select leaf_name,price,views,leaf_img from tea_leaf group by views desc having views > 100";
	var sqlArr=[];
	var callBack=(err,data)=>{
		if(err){
			console.log('连接失败',err)
		}else{
			res.send({
				'list':data,
				'code':200
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//请求茶叶分类列表

//价格区间和产地请求
router.post("/api/Leaflist/filter",function(req,res,next){
	//字符串数据，到sql语句中会包含引号，导致连接出错
	const lowp=req.body.princeBottm
	const higp=req.body.princeTop
	const pos=req.body.position
	const pageSize=req.body.pageSize
	const pageNum=(req.body.pageNum-1)*pageSize
	const price=req.body.sortpri
	const buy=req.body.sortbuy
	const def=req.body.sortdef
	var sqlArr=[]
	var sql=""
	if(def){
		sql='select * from tea_leaf where price >=? and price <=? and leaf_name like "%"?"%" limit ?,? ;';
		
	}else if(price){
		sql='select * from tea_leaf where price >=? and price <=? and leaf_name like "%"?"%" order by price limit ?,? ;';
	}else{
		sql='select * from tea_leaf where price >=? and price <=? and leaf_name like "%"?"%" order by pvo desc limit ?,? ;';
	}
	sqlArr=[lowp,higp,pos,pageNum,pageSize]
	
	var callBack=(err,data)=>{
		if(err){		
			console.log('--1连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//搜索路由
router.post("/api/Leaflist/search",function(req,res,next){
	const pageSize=req.body.pageSize
	const pageNum=(req.body.pageNum-1)*pageSize
	const searchval=req.body.searchVal
	const price=req.body.sortpri
	const buy=req.body.sortbuy
	const def=req.body.sortdef
	var sqlArr=[]
	var sql=""
	if(def){
		sql='select * from tea_leaf where leaf_name like "%"?"%" limit ?,?;';	
	}
	else if(price){
		sql='select * from tea_leaf where leaf_name like "%"?"%" order by price limit ?,?;';
	}else{
		sql='select * from tea_leaf where leaf_name like "%"?"%" order by pvo desc limit ?,?;';
	}
	sqlArr=[searchval,pageNum,pageSize]
	var callBack=(err,data)=>{
		if(err){
			
			console.log('--1连接失败--')
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//绿茶
router.post("/api/Leaflist",function(req,res,next){
	
	const codeTy=req.body.leafType
	const pageSize=req.body.pageSize
	const pageNum=(req.body.pageNum-1)*pageSize
	const searchval=req.body.searchVal
	const price=req.body.sortpri
	const buy=req.body.sortbuy
	const def=req.body.sortdef
	var sqlArr=[]
	var sql=""
	if(def){
		sql="select * from tea_leaf where leaf_type=? limit ?,?;";
		sqlArr=[codeTy,pageNum,pageSize]
	}else if(price){
		sql='select * from tea_leaf where leaf_type=? order by price limit ?,?;'
		sqlArr=[codeTy,pageNum,pageSize]
	}else if(buy){
		sql='select * from tea_leaf where leaf_type=? order by pvo desc limit ?,? ;'
		sqlArr=[codeTy,pageNum,pageSize]
	}else{
		sql='select * from tea_leaf where leaf_name like "%"?"%" limit ?,?;';
		sqlArr=[searchval,pageNum,pageSize]
	}
	
	var callBack=(err,data)=>{
		if(err){
			
			console.log('--1连接失败--')
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//请求分类茶叶页数
router.post("/api/Listcount",function(req,res,next){
	const codeTy=req.body.leafType
	const searchval=req.body.searchVal
	const lowp=req.body.princeBottm
	const higp=req.body.princeTop
	const pos=req.body.position
	var sqlArr=[]
	var sql=""
	if(searchval!=''){
		sql='select count(1) as total from tea_leaf where leaf_name like "%"?"%";'
		sqlArr=[searchval]
	}else if(lowp!=0){
		sql='select count(1) as total from tea_leaf where price >=? and price <=? and leaf_name like "%"?"%" ;'
		sqlArr=[lowp,higp,pos]
	}else{
		sql="select count(1) as total from tea_leaf  where leaf_type=? ;";
		sqlArr=[codeTy]
	}
	var callBack=(err,data)=>{
		if(err){
			console.log('--2连接失败--',err)
		}else{
			res.send({
				'count':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//请求茶叶对应评论
router.get("/api/Leaflist/comment",function(req,res,next){
	var leafId = req.query.leafid
	console.log(leafId)
	var sql="select tc.*, tu.user_name,tu.user_logo from tea_comment tc ,tea_users tu where tc.leaf_id=? and tc.user_id=tu.user_id "
	var sqlArr=[leafId]
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//购物车查询接口
router.get("/api/cart/find",function(req,res,next){
	const uid=req.query.userid
	console.log(uid)
	const sqlArr=[uid]
	var sql="select * from tea_cart where user_id=?"
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//购物车添加接口
router.post("/api/cart/add",function(req,res,next){
	
	const uid=req.body.userId
	const lid=req.body.leafId
	const lname=req.body.leafName
	const limg=req.body.leafImg
	const lnum=req.body.leafNum
	const lcount=req.body.leafCount
	const ctime=req.body.cartTime
	const price=req.body.price
	var sqlArr=[uid,lid,lname,limg,lnum,lcount,ctime,price]
	var sql="INSERT INTO tea_cart (user_id,leaf_id,leaf_name,leaf_img,leaf_num,leaf_count,cart_time,leaf_price) VALUES(?,?,?,?,?,?,?,?)"
	
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//购物车删除接口
router.post("/api/cart/del",function(req,res,next){
	let cs=req.body
	console.log(cs)
	let sqlArr=cs
	console.log(sqlArr)
	let sql=`delete from tea_cart where cart_id in (${sqlArr})`
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
router.post("/api/star/add",function(req,res,next){
	let uid=req.body.userid
	let lid=req.body.leafid
	let time=req.body.time
	let sqlArr=[uid,lid,time]
	let sql=`insert into tea_store (user_id,leaf_id,store_time) values (?,?,?)`
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
router.post("/api/star/del",function(req,res,next){
	let uid=req.body.userid
	let lid=req.body.leafid
	let sqlArr=[uid,lid]
	let sql=`delete from tea_store where user_id=? and leaf_id=?`
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'comlist':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
});
//查找收藏夹
router.post("/api/star/find",function(req,res,next){
	let id=req.body.userid
	let sqlArr=[id]
	let sql="SELECT tl.leaf_id,tl.leaf_name,tl.price,tl.views from tea_leaf tl,tea_store ts where ts.user_id=? and ts.leaf_id=tl.leaf_id"
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
//更改库存和销售数
router.post("/api/change/store",function(req,res,next){
	let lid=req.body.leafid
	let num=req.body.num
	let count =req.body.count
	let sqlArr=[num,count,lid]
	let sql="UPDATE tea_leaf set num=num-(?*?),pvo=pvo+1 where leaf_id =?"
	console.log(sqlArr)
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
//订单
router.post("/api/order/add",function(req,res,next){
	let code=req.body.ordercode
	let uid=req.body.userid
	let lid=req.body.leafid
	let num=req.body.num
	let price=req.body.price
	let date=req.body.date
	let state=req.body.state
	let count=req.body.count
	let sqlArr=[code,uid,lid,num,count,price,date,state]
	let sql="INSERT INTO tea_order(order_code,user_id,leaf_id,oreder_num,order_count,ordder_price,order_date,state) VALUES(?,?,?,?,?,?,?,?)"
	console.log(sqlArr)
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
router.post("/api/oadres/add",function(req,res,next){
	let code=req.body.ordercode
	let adres=req.body.address
	let phone=req.body.phone
	let username=req.body.username
	let sqlArr=[code,username,phone,adres]
	let sql="insert into tea_oadres (adrs_odcode,adrs_ctect,adrs_phone,adrs_adress) values(?,?,?,?)"
	console.log(sqlArr)
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})

//订单
//待发货,待收货，所有订单，代付款，售后
router.get("/api/order/get",function(req,res,next){
	let uid=req.query.uid
	let type=req.query.typeid
	let sqlArr
	let sql=""
	if(type==0){
		sqlArr=[uid]
		sql='select td.*,tl.leaf_name,tl.leaf_img from tea_order td, tea_leaf tl where td.user_id=? and td.leaf_id=tl.leaf_id'
	}else{
		sqlArr=[uid,type]
		sql='select td.*,tl.leaf_name,tl.leaf_img from tea_order td, tea_leaf tl where td.user_id=? and td.state=? and td.leaf_id=tl.leaf_id'
	}
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
//修改订单状态
router.get("/api/order/update",function(req,res,next){
	let orderid=req.query.orderid
	let type=req.query.typeid
	let sqlArr=[type,orderid]
	let sql="update tea_order set state=? where order_id=?"
	
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
//删除订单
router.get("/api/order/del",function(req,res,next){
	let orderid=req.query.orderid
	let sqlArr=[orderid]
	let sql="delete from tea_order where order_id=?"
	
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
//添加评论
router.post("/api/comment/add",function(req,res,next){
	let uid=req.body.uid
	let text=req.body.comtext
	let leafid=req.body.leafid
	let leafnum=req.body.leafnum
	let date=req.body.date
	let sqlArr=[uid,leafid,leafnum,text,date]
	let sql="insert into tea_comment(user_id,leaf_id,leaf_num,com_text,com_time) values(?,?,?,?,?)"
	var callBack=(err,data)=>{
		if(err){
			console.log('--连接失败--',err)
		}else{
			res.send({
				'list':data,
				'code':0
			})
		}
	}
	db.sqlConnection(sql,sqlArr,callBack);
})
module.exports = router;
